﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using FreeOnlineApplication.Class.Entity;
using MySql.Data.MySqlClient;
using System.Configuration;
using System.Collections;

namespace FreeOnlineApplication.Class.Repository
{
    public class CustomerRepository
    {
        protected MySqlConnection m_connection;
        MySqlCommand aCommand = new MySqlCommand();

        public CustomerRepository(MySqlConnection connection )
        {
            m_connection = connection;
            aCommand.Connection = m_connection;
        }
        public void Insert(Customer cust)
        {
            string insertSQL = String.Format(@"insert into table_customer 
                (
                    customer_code,
                    customer_idno,
                    customer_name,
                    customer_address,
                    customer_email,
                    customer_phone,
                    customer_job,
                    customer_gender,
                    customer_dob,
                    unit_id) 
                VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}',{9})",
                cust.CODE, cust.ID_NO, cust.NAME, cust.ADDRESS,
                cust.EMAIL, cust.PHONE, cust.JOB, cust.GENDER, cust.DOB.ToString(Tools.DATE_FORMAT),
                cust.UNIT.ID);
            aCommand.CommandText = insertSQL;
            aCommand.ExecuteNonQuery();
            cust.ID = GetMaximumId();
        }
        public Customer GetById(int id)
        {
            string getByID = String.Format("select * from table_customer where customer_id = {0}", id);
            aCommand.CommandText = getByID;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            Customer cust = transform(aReader);
            aReader.Close();
            return cust;
        }
        public void Update(Customer cust)
        {
            string updateSQL = String.Format(@"update table_customer set 
                customer_code = '{0}', 
                customer_idno = '{1}', 
                customer_name='{2}',
                customer_address='{3}',
                customer_email='{4}',
                customer_phone='{5}',
                customer_job='{6}',
                customer_gender='{7}',
                customer_dob='{8}',
                unit_id = {9}
                where customer_id = {10}",
                cust.CODE, cust.ID_NO, cust.NAME, cust.ADDRESS, cust.EMAIL,
                cust.PHONE, cust.JOB, cust.GENDER,
                cust.DOB.ToString(Tools.DATE_FORMAT),
                cust.UNIT.ID, cust.ID);
            aCommand.CommandText = updateSQL;
            aCommand.ExecuteNonQuery();
        }
        public void Delete(Customer cust)
        {
            string deleteSQL = "delete from table_customer where customer_id = " + cust.ID;
            aCommand.CommandText = deleteSQL;
            aCommand.ExecuteNonQuery();
        }
        public int GetMaximumId()
        {
            string getByID = String.Format("SELECT max(customer_id) from table_customer");
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public int GetCount(int unit)
        {
            string getByID = String.Format("SELECT count(*) from table_customer where unit_id = {0}", unit);
            aCommand.CommandText = getByID;
            object r = aCommand.ExecuteScalar();
            int aReader = r is System.DBNull ? 0 : Convert.ToInt32(r);
            return aReader;
        }
        public IList GetAll(int cust)
        {
            string sql = String.Format("select * from table_customer where unit_id = {0}", cust);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        public IList GetAllSearch(string find, int unit)
        {
            string sql = String.Format(@"select * from table_customer where 
            concat(customer_code, customer_idno, customer_name,
                customer_address, customer_email, customer_phone, customer_job,
                customer_gender) like '%{0}%' and unit_id = {1}", find, unit);
            aCommand.CommandText = sql;
            MySqlDataReader aReader = aCommand.ExecuteReader();
            IList users = transformList(aReader);
            aReader.Close();
            return users;
        }
        private Customer transform(MySqlDataReader aReader)
        {
            Customer cust = null;
            while (aReader.Read())
            {
                cust = new Customer();
                cust.ID = Convert.ToInt32(aReader["customer_id"]);
                cust.CODE = aReader["customer_code"].ToString();
                cust.ID_NO = aReader["customer_idno"].ToString();
                cust.NAME = aReader["customer_name"].ToString();
                cust.ADDRESS = aReader["customer_address"].ToString();
                cust.EMAIL = aReader["customer_email"].ToString();
                cust.PHONE = aReader["customer_phone"].ToString();
                cust.JOB = aReader["customer_job"].ToString();
                cust.GENDER = aReader["customer_gender"].ToString();
                cust.DOB = Convert.ToDateTime(aReader["customer_dob"]);
                cust.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
            }
            return cust;
        }
        private IList transformList(MySqlDataReader aReader)
        {
            IList result = new ArrayList();
            while (aReader.Read())
            {
                Customer cust = new Customer();
                cust.ID = Convert.ToInt32(aReader["customer_id"]);
                cust.CODE = aReader["customer_code"].ToString();
                cust.ID_NO = aReader["customer_idno"].ToString();
                cust.NAME = aReader["customer_name"].ToString();
                cust.ADDRESS = aReader["customer_address"].ToString();
                cust.EMAIL = aReader["customer_email"].ToString();
                cust.PHONE = aReader["customer_phone"].ToString();
                cust.JOB = aReader["customer_job"].ToString();
                cust.GENDER = aReader["customer_gender"].ToString();
                cust.DOB = Convert.ToDateTime(aReader["customer_dob"]);
                cust.UNIT = new Unit(Convert.ToInt32(aReader["unit_id"]));
                result.Add(cust);
            }
            return result;
        }
    }
}
